package cs4280.ass2.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

import cs4280.ass2.entity.Ticket;

public class TicketDao extends BaseDao {
	private static final Logger logger = Logger.getLogger(TicketDao.class);
	private static final String _SELECT_BY_ID = "select * from TICKET where ID = ?";
	private static final String _SELECT_ALL = "select * from TICKET order by id desc";
	private static final String _SELECT_USER_ACTIVE_TICKET = "SELECT t.id, m.name, ssd.seat_location, t.app_user_id, t.ticket_status  FROM TICKET t, SCHEDULE_SEAT_DETAIL ssd, SCHEDULE s, MOVIE m WHERE t.schedule_seat_detail_id = ssd.id AND s.movie_id = m.id AND t.app_user_id = ?";
	private static final String _INSERT_NEW_TICKET = "INSERT INTO [TICKET] (CREATE_DATE, CREATE_USER, MODIFY_DATE, MODIFY_USER,  APP_USER_ID, SCHEDULE_SEAT_DETAIL_ID, TICKET_STATUS) VALUES (?,?,?,?,?,?,?)";
	private static final String _UPDATE = "";
	private static final String _DELETE_TICKET = "UPDATE TICKET SET TICKET_STATUS = '" + Ticket.TICKET_STATUS_DELETED + "' WHERE ID = ?";
	private static final String _REQUEST_REFUND_TICKET = "UPDATE TICKET SET TICKET_STATUS = '" + Ticket.TICKET_STATUS_PENDING + "' WHERE ID = ?";
	private static final String _APPROVE_REFUND_TICKET = "UPDATE TICKET SET TICKET_STATUS = '" + Ticket.TICKET_STATUS_REFUNDED + "' WHERE ID = ?";
	private static TicketDao ticketDao = null;

	public static TicketDao getInstance() {
		if (ticketDao == null)
			ticketDao = new TicketDao();
		return ticketDao;
	}

	public Ticket retrieveTicket(long id) {
		Ticket ticket = null;
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			pstmt = connection.prepareStatement(_SELECT_BY_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			pstmt.setLong(1, id);
			result = pstmt.executeQuery();
			if (result.next()) {
				ticket = new Ticket();
				ticket.setId(result.getLong("id"));
				ticket.setAppUserId(result.getLong("APP_USER_ID"));
				ticket.setScheduleSeatDetailId(result.getLong("SCHEDULE_SEAT_DETAIL_ID"));
				ticket.setTicketStatus(result.getString("TICKET_STATUS"));
				ticket.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				ticket.setCreateUser(result.getLong("create_user"));
				ticket.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				ticket.setModifyUser(result.getLong("modify_user"));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveTicket ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(pstmt);
			DbUtils.closeQuietly(connection);
		}

		return ticket;
	}
	
	public List<Ticket> retrieveAllTicket() {
		List<Ticket> ticketList = new ArrayList<Ticket>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL);

			Ticket ticket;
			while (result.next()) {
				ticket = this.retrieveTicket(result.getLong("id"));
				ticketList.add(ticket);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve ticket ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return ticketList;
	}

	public void addTicket(Ticket ticket) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_INSERT_NEW_TICKET,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.setTimestamp(1, toSQLDate(ticket.getCreateDate()));
			statement.setLong(2, ticket.getCreateUser());
			statement.setTimestamp(3, toSQLDate(ticket.getModifyDate()));
			statement.setLong(4, ticket.getModifyUser());
			statement.setLong(5, ticket.getAppUserId());
			statement.setLong(6, ticket.getScheduleSeatDetailId());
			statement.setString(7, ticket.getTicketStatus());
			
			int rows = statement.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to create ticket ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}

	public void updateTicket(Ticket ticket) {

	}

	public List<Ticket> retrieveUserActiveTicket() {
		List<Ticket> ticketList = new ArrayList<Ticket>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		/*try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_USER_ACTIVE_TICKET);

			Ticket ticket;
			while (result.next()) {
				ticket = this.retrieveTicket(result.getLong("id"));
				ticketList.add(ticket);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve ticket ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}*/
		return ticketList;
	}
	
	public void requestRefundTicket(long id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_REQUEST_REFUND_TICKET,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			statement.setLong(1, id);
			
			// for this deletion, we assumed the provided id is exist.
			int rows = statement.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to request refund ticket ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}
	
	public void approveRefundTicket(long id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_APPROVE_REFUND_TICKET,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			statement.setLong(1, id);
			
			// for this deletion, we assumed the provided id is exist.
			int rows = statement.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to refund ticket ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}
	
	public void deleteTicket(long id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_DELETE_TICKET,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			statement.setLong(1, id);
			
			// for this deletion, we assumed the provided id is exist.
			int rows = statement.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to delete ticket ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}

}
